USE [BSM]
GO
/****** Object:  View [dbo].[CurrentAccountsBalance]    Script Date: 12/12/2014 11:02:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[CurrentAccountsBalance]
AS
SELECT     SessionID, SessionTitle, AccountNo, AccountTitle, ISNULL(SUM(OpeningBal), 0) AS OpeningBal, ISNULL(SUM(Debit), 0) AS Debit, ISNULL(SUM(Credit), 
                      0) AS Credit, ISNULL(SUM(CurrentBalance), 0) AS CurrentBalance, VoucherDate
FROM         (SELECT     dbo.SessionInfo.SessionID, dbo.SessionInfo.SessionTitle, dbo.Accounts.AccountNo, dbo.Accounts.AccountTitle, 
                                              dbo.SessionBalances.OpeningBal, 0 AS Credit, 0 AS Debit, dbo.SessionBalances.OpeningBal AS CurrentBalance, 
                                              dbo.SessionInfo.SessionFrom AS VoucherDate
                       FROM          dbo.Accounts RIGHT OUTER JOIN
                                              dbo.SessionBalances ON dbo.Accounts.AccountNo = dbo.SessionBalances.AccountNo LEFT OUTER JOIN
                                              dbo.SessionInfo ON dbo.SessionBalances.SessionID = dbo.SessionInfo.SessionID
                       WHERE      (dbo.SessionInfo.CurrentSession = 1)
                       UNION ALL
                       SELECT     SessionID, SessionTitle, AccountNo, AccountTitle, 0 AS OpeningBalance, ISNULL(SUM(Debit), 0) AS Debit, ISNULL(SUM(Credit), 0) AS Credit, 
                                             ISNULL(SUM(Debit), 0) - ISNULL(SUM(Credit), 0) AS CurrentBalance, VoucherDate
                       FROM         dbo.VW_Vouchers
                       WHERE     (CurrentSession = 1)
                       GROUP BY SessionID, SessionTitle, AccountNo, AccountTitle, VoucherDate) AS TempTable
GROUP BY SessionID, SessionTitle, AccountNo, AccountTitle, VoucherDate

--------------------------------------------------------------------------

/****** Object:  View [dbo].[vw_Accounts]    Script Date: 12/12/2014 11:02:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[vw_Accounts]
AS
SELECT     dbo.Area.RegionID, dbo.Region.RegionCode, dbo.Region.RegionName, dbo.Region.RegionNameUrdu, dbo.Accounts.AreaID, dbo.Area.AreaCode, 
                      dbo.Area.AreaName, dbo.Area.AreaNameUrdu, dbo.Accounts.AccountNo, dbo.Accounts.AccountTitle, dbo.Accounts.AccountType, 
                      dbo.Accounts.AccountNature, dbo.Accounts.ParentAccount, dbo.Accounts.AccountLevel, dbo.Accounts.Remarks, dbo.Accounts.Discontinue, 
                      dbo.Accounts.ReadOnly, dbo.Accounts.ThroughSystemOnly, dbo.Accounts.CellNo, dbo.Accounts.Phone, dbo.CurrentAccountsBalance.OpeningBal, 
                      dbo.CurrentAccountsBalance.Debit, dbo.CurrentAccountsBalance.Credit, dbo.CurrentAccountsBalance.CurrentBalance, 
                      dbo.CurrentAccountsBalance.VoucherDate
FROM         dbo.CurrentAccountsBalance RIGHT OUTER JOIN
                      dbo.Accounts ON dbo.CurrentAccountsBalance.AccountNo = dbo.Accounts.AccountNo LEFT OUTER JOIN
                      dbo.Region INNER JOIN
                      dbo.Area ON dbo.Region.RegionID = dbo.Area.RegionID ON dbo.Accounts.AreaID = dbo.Area.AreaID